﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ICeFramework.Common.DataLayer;
using System.Data;
using System.Data.SqlClient;
using ICeFramework.Common.Configuration;
using ICeFramework.DataAccess.DataLayer.Enums;
using ICeFramework.Utilities.Security;
using ICeFramework.DataAccess.Proxy;

namespace ICeFramework.DataAccess.DataLayer.MsSQL
{
    public class MsSQLDataAccessService : DataAccessService
    {
        private SqlDataAdapter _adapter;

        private const string _paramToken = "@";
        #region public override string ParamToken
        /// <summary>
        /// Gets the ParamToken of the MsSQLDataAccessService
        /// </summary>
        /// <value></value>
        public override string ParamToken
        {
            get { return _paramToken; }
        }
        #endregion

        #region public MsSQLDataAccessService()
        /// <summary>
        /// Initializes a new instance of the <b>MsSQLDataAccessService</b> class.
        /// </summary>
        public MsSQLDataAccessService()
            : this(string.Empty)
        {
        }
        #endregion

        #region public MsSQLDataAccessService(string connectionName)
        /// <summary>
        /// Initializes a new instance of the <b>MsSQLDataAccessService</b> class.
        /// </summary>
        /// <param name="connectionName"></param>
        public MsSQLDataAccessService(string connectionName)
        {
            base.Connection = new SqlConnection(ConfigurationHelper.GetConnection(connectionName));
        }
        #endregion

        #region public MsSQLDataAccessService(Uri connectionUrl)
        /// <summary>
        /// Initializes a new instance of the <b>MsSQLDataAccessService</b> class.
        /// </summary>
        /// <param name="connectionUrl"></param>
        public MsSQLDataAccessService(Uri connectionUrl, string userName, string password)
        {
            base.Proxy = new DALProxy(connectionUrl.ToString(), Hash.GetMd5Hash(userName), Hash.GetMd5Hash(password));
        }
        #endregion

        #region public override void Fill(DataTable table, EntityFilter filter, EntityOrder order, IDbTransaction transaction)
        /// <summary>
        /// 
        /// </summary>
        /// <param name="table"></param>
        /// <param name="filter"></param>
        /// <param name="order"></param>
        /// <param name="transaction"></param>
        public override void Fill(DataTable table, EntityFilter filter, EntityOrder order, IDbTransaction transaction)
        {
            if (base.Connection != null)
            {
                if (table.DataSet == null)
                {
                    DataSet ds = new DataSet();
                    ds.Tables.Add(table);
                }

                SelectCommand cmd = new SelectCommand(filter, order, table.TableName, transaction);
                if (_adapter == null)
                {
                    _adapter = new SqlDataAdapter(cmd.BuildCommandText(_paramToken), base.Connection.ConnectionString);
                    _adapter.SelectCommand.Transaction = (SqlTransaction)cmd.Transaction;
                    _adapter.FillError += new FillErrorEventHandler(adapter_FillError);
                    _adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                }

                try
                {
                    _adapter.Fill(table);
                }
                catch (Exception)
                {
                    throw;
                }
            }
            else if (base.Proxy != null)
            {
                base.Proxy.Fill(table, filter, order, transaction);
            }
        }
        #endregion

        #region void adapter_FillError(object sender, FillErrorEventArgs e)
        /// <summary>
        /// This method is called when the adapter's FillError event has been fired.
        /// </summary>
        /// <param name="sender">The <see cref="object"/> that fired the event.</param>
        /// <param name="e">The <see cref="FillErrorEventArgs"/> of the event.</param>
        void adapter_FillError(object sender, FillErrorEventArgs e)
        {
            //throw new NotImplementedException();
        }
        #endregion

        #region public override void Fill(DataTable table, EntityFilter filter, EntityOrder order, int pageIndex, int pageSize, out long totalRowsCount, IDbTransaction transaction)
        /// <summary>
        /// 
        /// </summary>
        /// <param name="table"></param>
        /// <param name="filter"></param>
        /// <param name="order"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalRowsCount"></param>
        /// <param name="transaction"></param>
        public override void Fill(DataTable table, EntityFilter filter, EntityOrder order, int pageIndex, int pageSize, out long totalRowsCount, IDbTransaction transaction)
        {
            throw new NotImplementedException();
        }
        #endregion

        #region public override void Fill(DataTable table, StoredProcedureDefinition spDefinition, EntityOrder order, IDbTransaction transaction)
        /// <summary>
        /// 
        /// </summary>
        /// <param name="table"></param>
        /// <param name="spDefinition"></param>
        /// <param name="order"></param>
        /// <param name="transaction"></param>
        public override void Fill(DataTable table, StoredProcedureDefinition spDefinition, EntityOrder order, IDbTransaction transaction)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            //SqlDataAdapter adapter = new SqlDataAdapter(cmd, base.Connection.ConnectionString);
        }
        #endregion

        public override int Count(DataTable table, EntityFilter filter)
        {
            if (base.Connection != null)
            {
                SelectCommand cmd = new SelectCommand(filter, null, table.TableName, null);
                cmd.Count = true;

                try
                {
                    return (int)Utils.ExecuteScalar(cmd.BuildCommandText(_paramToken), null);
                }
                catch (Exception)
                {
                    throw;
                }
            }
            else if (base.Proxy != null)
            {
                return base.Proxy.Count(table, filter);
            }
            return -1;
        }

        #region public override void Update(DataTable table, List<EntityColumn> columns, IDbTransaction transaction)
        /// <summary>
        /// 
        /// </summary>
        /// <param name="table"></param>
        /// <param name="columns"></param>
        /// <param name="transaction"></param>
        public override void Update(DataTable table, List<EntityColumn> columns, IDbTransaction transaction)
        {
            if (base.Connection != null)
            {
                if (table.DataSet == null)
                {
                    throw new DataException("Cannot update a table before it was filled out.");
                }

                bool releaseConnection = transaction == null;
                try
                {
                    if (releaseConnection)
                    {
                        base.Connection.Open();
                        transaction = base.Connection.BeginTransaction();
                    }

                    int idx = 0;
                    while (idx < table.Rows.Count)
                    {
                        bool deleted = table.Rows[idx].RowState == DataRowState.Deleted;

                        Update(table.Rows[idx], columns, transaction);

                        if (!deleted)
                        {
                            idx++;
                        }
                    }
                    transaction.Commit();
                }
                catch
                {
                    if (releaseConnection)
                    {
                        transaction.Rollback();
                    }
                    throw;
                }
                finally
                {
                    if (releaseConnection)
                    {
                        if (base.Connection != null && base.Connection.State != ConnectionState.Closed)
                        {
                            base.Connection.Close();
                        }
                        transaction = null;
                        base.Connection = null;
                    }
                }
            }
            else
            {
                base.Proxy.Update(table, columns, transaction);
            }
        }
        #endregion

        #region public override void Update(DataRow row, List<EntityColumn> columns, IDbTransaction transaction)
        /// <summary>
        /// 
        /// </summary>
        /// <param name="row"></param>
        /// <param name="columns"></param>
        /// <param name="transaction"></param>
        public override void Update(DataRow row, List<EntityColumn> columns, IDbTransaction transaction)
        {
            if (_adapter == null)
            {
                _adapter = new SqlDataAdapter(string.Empty, base.Connection.ConnectionString);
            }

            DBCommand cmd = null;

            switch (row.RowState)
            {
                case DataRowState.Added:
                    cmd = new InsertCommand(row.Table.TableName, transaction);
                    break;
                case DataRowState.Deleted:
                    cmd = new DeleteCommand(row.Table.TableName, transaction);
                    break;
                case DataRowState.Modified:
                    row.EndEdit();
                    cmd = new UpdateCommand(row.Table.TableName, transaction);
                    break;
                case DataRowState.Detached:
                    throw new DataException(string.Format("Unsupported row state ({0})", row.RowState));
            }

            if (cmd == null)
            {
                return;
            }

            cmd.Parameters = base.GetCommandParameters(columns, row, cmd.CommandType);

            SqlCommand sqlCmd = new SqlCommand(cmd.BuildCommandText(_paramToken), (SqlConnection)base.Connection);
            foreach (DataParameter param in cmd.Parameters)
            {
                SqlParameter p = new SqlParameter(param.ParameterName, param.DbType);
                p.Value = param.Value;
                sqlCmd.Parameters.Add(p);
            }
            switch (cmd.CommandType)
            {
                case DBCommandType.Insert:
                    _adapter.InsertCommand = sqlCmd;
                    break;
                case DBCommandType.Update:
                    _adapter.UpdateCommand = sqlCmd;
                    break;
                case DBCommandType.Delete:
                    _adapter.DeleteCommand = sqlCmd;
                    break;
            }

            bool releaseConnection = transaction == null;
            try
            {
                if (releaseConnection)
                {
                    base.Connection.Open();
                    transaction = base.Connection.BeginTransaction();
                }

                sqlCmd.Transaction = (SqlTransaction)transaction;
                _adapter.Update(new DataRow[] { row });

                if (cmd.CommandType == DBCommandType.Insert)
                {
                    SetIdentity(row, columns, transaction);
                    if (releaseConnection)
                    {
                        transaction.Commit();
                    }
                }
                else if (releaseConnection)
                {
                    transaction.Commit();
                }
            }
            catch
            {
                if (releaseConnection)
                {
                    transaction.Rollback();
                }
                throw;
            }
            finally
            {
                if (releaseConnection)
                {
                    if (base.Connection != null && base.Connection.State != ConnectionState.Closed)
                    {
                        base.Connection.Close();
                    }
                    cmd = null;
                    transaction = null;
                    base.Connection = null;
                }
            }
        }
        #endregion

        #region private void SetIdentity(DataRow row, List<EntityColumn> columns, IDbTransaction transaction)
        /// <summary>
        /// 
        /// </summary>
        /// <param name="row"></param>
        /// <param name="columns"></param>
        /// <param name="transaction"></param>
        private void SetIdentity(DataRow row, List<EntityColumn> columns, IDbTransaction transaction)
        {
            object id = Utils.ExecuteScalar("SELECT @@IDENTITY", transaction);
            foreach (EntityColumn column in columns)
            {
                if (column.IsInPrimaryKeys)
                {
                    row.Table.Columns[column.Name].ReadOnly = false;
                    row[column.Name] = id;
                    row.Table.Columns[column.Name].ReadOnly = true;
                }
            }
        }
        #endregion
    }
}
